package com.tdnn.fin.payments.control;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Logger;
import java.text.SimpleDateFormat;

import javax.ejb.Stateless;

import com.tdnn.fin.payments.entity.Payment;

@Stateless
public class PaymentControl {
	public List<Payment> listCompanyPaymentByPeriod(Date date1, Date date2, Float currencyRate) {

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Payment> paymentList = new ArrayList<Payment>();
        Logger l = Logger.getLogger("PaymentControl");
       
        try{
            conn = DriverManager.getConnection("jdbc:mysql://192.168.90.200:3306/tdnnfin","root","root");
            System.out.println("Connection established successfully.");
            
            String dateCondition = "";
            String query = "";
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                       
            if(date1!=null){
                dateCondition=dateCondition+" WHERE PTABLE.PDATE >= '"+sdf.format(date1)+"' ";
            }
            if(date2!=null){
                if(dateCondition.equals("")){
                    dateCondition=dateCondition+" WHERE PTABLE.PDATE <= '"+sdf.format(date2)+"' ";
                } else {
                    dateCondition=dateCondition+" AND PTABLE.PDATE <= '"+sdf.format(date2)+"' ";
                }
            }

            query=query+"SELECT PTABLE.COMPANYID,COMPANIES.NAME," +
                        "COMPANIES.GROUPID,COMPANYGROUPS.NAME AS GNAME,SUM(IF(BANK=1,AMOUNT,0)) AS BAMOUNT," +
                        "SUM(IF(BANK=0,AMOUNT,0)) AS CAMOUNT " +
                        "FROM PAYMENTSS AS PTABLE "+
                        " LEFT JOIN COMPANIES ON PTABLE.COMPANYID=COMPANIES.COMPANYID " +
                        "LEFT JOIN COMPANYGROUPS ON COMPANIES.GROUPID=COMPANYGROUPS.GROUPID "+dateCondition+
                        "GROUP BY COMPANYID ORDER BY GROUPID,COMPANYID;";

            l.info("SQL STATEMENT:\n"+query);
            
            stmt=conn.createStatement();
            rs=stmt.executeQuery(query);
            
            int groupId = 0;
            String groupName = "";
            
            Double bamountTotal = Double.valueOf(0);
            Double camountTotal = Double.valueOf(0);
            Double bamountGrandTotal = Double.valueOf(0);
            Double camountGrandTotal = Double.valueOf(0);
                                    
            while (rs.next()) {
                Payment cpbp = new Payment();
                int curGroupId = rs.getInt("GROUPID");
                
                cpbp.setCompanyName(rs.getString("NAME"));
                cpbp.setBankPayment(rs.getDouble("BAMOUNT"));
                cpbp.setCashPayment(rs.getDouble("CAMOUNT"));
                cpbp.setTotalPaymentInCurrency(currencyRate);
                
                if (groupId!=curGroupId && groupId>0){
                	Payment cpbpt = new Payment();
                	cpbpt.setCompanyName("ИТОГО ПО "+groupName);
                    cpbpt.setBankPayment(bamountTotal);
                    cpbpt.setCashPayment(camountTotal);
                    cpbpt.setTotalPaymentInCurrency(currencyRate);
                    cpbpt.setTotalRow(true);
                    
                    paymentList.add(cpbpt);   
                    
                    bamountTotal = Double.valueOf(0);
                    camountTotal = Double.valueOf(0);
                }
                groupId = curGroupId; 
                groupName=rs.getString("GNAME");
                
                bamountTotal=bamountTotal+cpbp.getBankPayment();
                camountTotal=camountTotal+cpbp.getCashPayment();
                
                bamountGrandTotal=bamountGrandTotal+cpbp.getBankPayment();
                camountGrandTotal=camountGrandTotal+cpbp.getCashPayment();
                
                paymentList.add(cpbp);
            }
            if(paymentList.size()>0){
            	// for group total
            	Payment cpbpt = new Payment();
            	cpbpt.setCompanyName("ИТОГО ПО "+groupName);
                cpbpt.setBankPayment(bamountTotal);
                cpbpt.setCashPayment(camountTotal);
                cpbpt.setTotalPaymentInCurrency(currencyRate);  
                cpbpt.setTotalRow(true);
                paymentList.add(cpbpt);
                
                // for grand total
                Payment cpbgt = new Payment();
            	cpbgt.setCompanyName("ВСЕГО");
                cpbgt.setBankPayment(bamountGrandTotal);
                cpbgt.setCashPayment(camountGrandTotal);
                cpbgt.setTotalPaymentInCurrency(currencyRate);
                cpbgt.setTotalRow(true);
                paymentList.add(cpbgt);
            }
        } catch (SQLException se) {
            l.info("SQL error: "+se.getMessage()+"\n\t code"+se.getErrorCode());
        } catch (Exception e) {
            l.info("Exception: "+e.getMessage());
            e.printStackTrace();
        } finally {
        	try {
        		rs.close();
        		stmt.close();
				conn.close();
			} catch (SQLException se) {
				l.info("SQL error: "+se.getMessage()+"\n\t code"+se.getErrorCode());
			}
        }
        return paymentList;
    }
}
